package ods_table

import (
	"database/sql"
	"errors"
	"fmt"
	"server/apps/ods/ods_field"
	"server/apps/ods/ods_odb"
	"server/global"
	"strings"

	"go.uber.org/zap"
)


type OdsDbTable struct {
	global.BaseModel

	SrcDataType string `json:"srcDataType" form:"srcDataType" gorm:"not null;column:srcDataType;comment:源数据类型;size:100;"`
	SrcDataAttach string `json:"srcDataAttach" form:"srcDataAttach" gorm:"column:srcDataAttach;comment:源数据来源;size:100;"`
	SrcDbId   uint   `json:"srcDbId" form:"srcDbId" gorm:"column:srcDbId;comment:源数据库id;size:100;"`
	SrcDbType string `json:"srcDbType" form:"srcDbType" gorm:"not null;column:srcDbType;comment:源数据库类型;size:100;"`
	SrcSchemaId   uint   `json:"srcSchemaId" form:"srcSchemaId" gorm:"not null;column:srcSchemaId;comment:源模式id;size:100;"`
	SrcSchemaType string `json:"srcSchemaType" form:"srcSchemaType" gorm:"column:srcSchemaType;comment:源模式类型;size:50;"`
	SrcSchemaName string `json:"srcSchemaName" form:"srcSchemaName" gorm:"column:srcSchemaName;comment:源模式名;size:50;"`
	SrcTbType    string `json:"srcTbType" form:"srcTbType" gorm:"not null;column:srcTbType;comment:源表类型;size:50;"`
	SrcTbName    string `json:"srcTbName" form:"srcTbName" gorm:"not null;column:srcTbName;comment:源表名;size:50;"`
	SrcTbComment string `json:"tbComment" form:"tbComment" gorm:"column:tbComment;comment:字段注释;size:100;"`
	SrcTbLabel   string `json:"srcTbLabel" form:"srcTbLabel" gorm:"column:srcTbLabel;comment:源表标签;size:50;"`
	SrcTbRows    int64  `json:"srcTbRows" form:"srcTbRows" gorm:"column:srcTbRows;comment:源表行数;size:50;"`
	PriCol string `json:"priCol" form:"priCol" gorm:"column:priCol;comment:主键列名;size:250;"`
	UniCol string `json:"uniCol" form:"uniCol" gorm:"column:uniCol;comment:唯一值列;size:250;"`
	Bsm string `json:"bsm" form:"bsm" gorm:"not null;column:bsm;comment:标识码;size:100;"`
	SrcSchemaTb string `json:"srcSchemaTb" form:"srcSchemaTb" gorm:"column:srcSchemaTb;comment:源模式表名;size:50;"`

	OdsDbType     string `json:"odsDbType" form:"odsDbType" gorm:"not null;column:odsDbType;comment:ods数据库类型;size:100;"`
	OdsSchemaName string `json:"odsSchemaName" form:"odsSchemaName" gorm:"not null;column:odsSchemaName;comment:ods模式名;size:50;"`
	OdsTbName     string `json:"odsTbName" form:"odsTbName" gorm:"not null;column:odsTbName;comment:ods表名;size:191;"`
	OdsTbComment  string `json:"odsTbComment" form:"odsTbComment" gorm:"column:odsTbComment;comment:ods表注释;size:100;"`
	OdsTbLabel    string `json:"odsTbLabel" form:"odsTbLabel" gorm:"column:odsTbLabel;comment:ods表标签;size:50;"`
	OdsTbLen      int64  `json:"odsTbLen" form:"odsTbLen" gorm:"column:odsTbLen;comment:ods表长度;size:50;"`
	OdsTbCategory string `json:"odsTbCategory" form:"odsTbCategory" gorm:"column:odsTbCategory;comment:表数据分类;size:50;"`
	OdsSchemaTb   string `json:"odsSchemaTb" form:"odsSchemaTb" gorm:"column:odsSchemaTb;comment:ods模式表名;size:50;"`
	ColsString string `json:"colsString" form:"colsString" gorm:"column:colsString;comment:字段名称字符串;size:1000;"`

	RtSync string `json:"rtSync" form:"rtSync" gorm:"column:rtSync;comment:实时同步开关;size:1000;"`

	Remarks string `json:"remarks" form:"remarks" gorm:"column:remarks;comment:备注;size:250;"`

	SrcIdSchemaTb string `json:"srcIdSchemaTb" form:"srcIdSchemaTb" gorm:"column:srcIdSchemaTb;comment:srcIdSchemaTb;size:100;"`

	ColsList []string `json:"colsList" gorm:"-"`
	InOds int64 `json:"inOds" gorm:"-"`
}


func (m *OdsDbTable) TableName() string {
	return "ods_db_table"
}


func (m *OdsDbTable) Create() error {
	err := global.SysDB.Create(m).Error
	return err
}


func (m *OdsDbTable) Save() error {
	err := global.SysDB.Save(&m).Error
	return err
}


func (m *OdsDbTable) SetBsm() error {
	if m.SrcDbId > 0 && m.SrcSchemaName != "" && m.SrcTbName != "" {
		m.Bsm = fmt.Sprintf("%d_%s_%s", m.SrcDbId, m.SrcSchemaName, m.SrcTbName)
		return nil
	}
	return errors.New("没有SrcDbId，或是srcSchemaName为空，或是SrcTbName为空")
}


func (m *OdsDbTable) SetSrcSchemaTb() error {
	if m.SrcSchemaName != "" && m.SrcTbName != "" {
		m.SrcSchemaTb = fmt.Sprintf("`%s`.`%s`", m.SrcSchemaName, m.SrcTbName)
		return nil
	}
	return errors.New("srcSchemaName为空，或是SrcTbName为空")
}


func (m *OdsDbTable) SetOdsSchemaTb() error {
	if m.OdsSchemaName != "" && m.OdsTbName != "" {
		m.OdsSchemaTb = fmt.Sprintf("`%s`.`%s`", m.OdsSchemaName, m.OdsTbName)
		return nil
	}
	return errors.New("OdsSchemaName为空，或是OdsTbName为空")
}


func (m *OdsDbTable) SetColsList() error {
	if m.ColsString != "" {
		m.ColsList = strings.Split(m.ColsString, ",")
		return nil
	}
	return errors.New("ColsString为空")
}

func (m *OdsDbTable) SetInit() error {
	var err error
	if err = m.SetBsm(); err != nil {
		return err
	}
	if err = m.SetColsList(); err != nil {
		return err
	}
	if err = m.SetSrcSchemaTb(); err != nil {
		return err
	}
	if err = m.SetOdsSchemaTb(); err != nil {
		return err
	}
	return nil
}


func (m *OdsDbTable) GetConn() (db *sql.DB, err error) {
	var odb ods_odb.OdsOdb
	var conn *sql.DB

	if m.SrcDbId > 0 {
		err = global.SysDB.Where("id = ?", m.SrcDbId).First(&odb).Error
		if err != nil {
			global.Logger.Error(fmt.Sprintf("获取源数据库信息失败,id=%d", m.SrcDbId))
			return nil, err
		}
		conn, err = odb.GetConn()
		return conn, err
	} else {
		return nil, fmt.Errorf("获取源数据库连接失败,id=%d", m.SrcDbId)
	}
}


func (m *OdsDbTable) GetPriUniKey() (priKey, uniKey string, err error) {
	global.Logger.Info("获取源表主键")
	priKey = ""
	uniKey = ""
	srcConn, err := m.GetConn()
	if err != nil {
		global.Logger.Info(" 获取数据库连接失败")
		return priKey, uniKey, err
	}

	switch m.SrcDbType {
	case "mysql":
		{
			priKey, uniKey, err = GetMysqlPriKey(srcConn, m.SrcSchemaName, m.SrcTbName)
			if err != nil {
				return priKey, uniKey, err
			}

			return priKey, uniKey, nil
		}
	default:
		{
			global.Logger.Info("数据库类型未支持:", zap.Error(errors.New(m.SrcDbType)))
		}
	}
	return priKey, uniKey, errors.New("获取源表主键失败")
}


func (m *OdsDbTable) GetOdbFlds() ([]ods_field.OdsDbField, error) {
	global.Logger.Info("获取源表字段信息")
	srcConn, err := m.GetConn()
	if err != nil {
		global.Logger.Info(" 获取数据库连接失败")
		return nil, err
	}
	switch m.SrcDbType {
	case "mysql":
		{
			global.Logger.Info("数据库类型: mysql")
			srcFlds, err1 := GetMysqlFlds(srcConn, m.SrcSchemaName, m.SrcTbName)
			if err1 != nil {
				return nil, err1
			}

			return srcFlds, nil
		}
	case "oracle":
		{
			global.Logger.Info("数据库类型: oracle")
			srcFlds, err1 := GetOracleTbFlds(srcConn, m.SrcSchemaName, m.SrcTbName)
			if err1 != nil {
				return nil, err1
			}

			return srcFlds, nil
		}
	default:
		{
			global.Logger.Info("数据库类型未支持:", zap.Error(errors.New(m.SrcDbType)))
		}
	}
	return nil, errors.New("获取源表字段失败")
}


func (m *OdsDbTable) GetSrcDataOne() (map[string]string, error) {
	var rowResults map[string]string
	err := m.SetSrcSchemaTb()
	if err != nil {
		return nil, err
	}
	srcConn, err := m.GetConn()
	if err != nil {
		return nil, err
	}

	var sqlData string
	switch m.SrcDbType {
	case "mysql":
		{
			sqlData = fmt.Sprintf("select * from %s limit 1;", m.SrcSchemaTb)
		}
	case "oracle":
		{
			sqlData = fmt.Sprintf("select * from %s where user='%s' and rownum<=1", m.SrcTbName, m.SrcSchemaName)
		}
	}

	query, err0 := srcConn.Query(sqlData)
	if err0 != nil {
		msg := fmt.Sprintf("查询示例数据失败:err:%v\n sql:%s", err0, sqlData)
		global.Logger.Error(msg)
		return nil, errors.New(msg)
	}

	cols, _ := query.Columns()
	values := make([][]byte, len(cols))
	scans := make([]interface{}, len(cols))
	for i := range values {
		scans[i] = &values[i]
	}


	for query.Next() { // --循环，让游标往下推
		if err := query.Scan(scans...); err != nil {
			return nil, err
		}

		row := make(map[string]string)

		for k, v := range values {
			key := cols[k]
			row[key] = string(v)
		}
		rowResults = row
	}
	return rowResults, nil
}


func GetMysqlPriKey(mysqlDb *sql.DB, schemaName string, tbName string) (priCol string, uniCol string, err error) {
	println("获取mysql源表的主键")
	priCol = ""
	uniCol = ""
	sqls := fmt.Sprintf("show full columns  from  `%s`.`%s`", schemaName, tbName)
	rows, err := mysqlDb.Query(sqls)
	if err != nil {
		msg := fmt.Sprintf("获取mysql源表的主键失败:")
		global.Logger.Error(msg, zap.Error(err))
		return priCol, uniCol, errors.New(msg)
	}
	for rows.Next() {
		var colName, colKey string
		var unused interface{}
		err = rows.Scan(
			&colName, // --Field
			&unused,  // --Type
			&unused,  // --Collation
			&unused,  // --Null
			&colKey,  // --Key
			&unused,  // --Default
			&unused,  // --Extra
			&unused,  // --Privileges
			&unused,  // --Comment
		)
		if err != nil {
			global.Logger.Info("取出ODB mysql表字段失败:")
			global.Logger.Error("取出ODB mysql表字段失败!", zap.Error(err))
			return priCol, uniCol, err
		}
		if strings.Contains(colKey, "PRI") {
			priCol = colName
		}
		if strings.Contains(colKey, "UNI") {
			uniCol = colName
		}
	}
	return priCol, uniCol, nil
}


func GetMysqlFlds(mysqlDb *sql.DB, schemaName string, tbName string) ([]ods_field.OdsDbField, error) {
	println("获取mysql某表的字段信息")
	sqls := fmt.Sprintf("show full columns  from  `%s`.`%s`", schemaName, tbName)
	var result []ods_field.OdsDbField
	rows, err := mysqlDb.Query(sqls)
	if err != nil {
		msg := fmt.Sprintf("查询ODB mysql表字段失败:")
		global.Logger.Error(msg, zap.Error(err))
		return nil, errors.New(msg)
	}
	for rows.Next() {
		var r ods_field.OdsDbField
		var unused interface{}
		err = rows.Scan(
			&r.ColName,    // --Field
			&r.ColType,    // --Type
			&unused,       // --Collation
			&r.IsNull,     // --Null
			&r.ColKey,     // --Key
			&unused,       // --Default
			&unused,       // --Extra
			&r.Privileges, // --Privileges
			&r.ColComment, // --Comment
		)
		if err != nil {
			global.Logger.Info("取出ODB mysql表字段失败:")
			global.Logger.Error("取出ODB mysql表字段失败!", zap.Error(err))
			return nil, err
		}
		result = append(result, r)
	}
	return result, nil
}


func GetOracleTbFlds(db *sql.DB, schemaName string, tbName string) ([]ods_field.OdsDbField, error) {
	global.Logger.Info("获取oracle某表的字段信息")
	sqlKey := fmt.Sprintf("select cu.COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '%s' and user='%s'", tbName, schemaName)
	global.Logger.Info(sqlKey)
	rows, err := db.Query(sqlKey)
	if err != nil {
		return nil, err
	}
	var colKeyList = make([]string, 0)
	var colKey string
	for rows.Next() {
		err := rows.Scan(&colKey)
		if err != nil {
			fmt.Printf("scan oracle failed, err:%v\n", err)
			return nil, err
		}
		colKeyList = append(colKeyList, colKey)
	}

	type SrcFld struct {
		ColName    *string `json:"colName"`
		IsNull     *string `json:"isNull"`
		DataType   *string `json:"dataType"`
		DataLength *int    `json:"dataLength"`
		ColComment *string `json:"colComment"`
	}
	sqlSelect := "a.COLUMN_NAME,a.NULLABLE,a.DATA_TYPE,a.DATA_LENGTH,b.COMMENTS"
	sqlTbFldStr := fmt.Sprintf("select %s FROM user_tab_columns a LEFT JOIN user_col_comments b ON a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME WHERE a.TABLE_NAME = '%s' and user='%s'", sqlSelect, tbName, schemaName)
	global.Logger.Info(sqlTbFldStr)

	var result []ods_field.OdsDbField

	rows, err = db.Query(sqlTbFldStr)
	if err != nil {
		global.Logger.Info("查询ODB oracle表字段失败:")
		global.Logger.Error("查询orcale表字段失败!", zap.Error(err))
		return nil, err
	}
	global.Logger.Info("获取所有字段详情")
	for rows.Next() {
		var f SrcFld
		var r ods_field.OdsDbField
		err = rows.Scan(
			&f.ColName,
			&f.IsNull,
			&f.DataType,
			&f.DataLength,
			&f.ColComment,
		)
		if err != nil {
			global.Logger.Info("取出ODB oracle表字段失败:")
			global.Logger.Error("取出ODB oracle表字段失败!", zap.Error(err))
			return nil, err
		}

		r.OdbSchema = schemaName
		r.OdbTbName = tbName

		r.ColName = *f.ColName
		if f.IsNull != nil {
			r.IsNull = *f.IsNull
		} else {
			r.IsNull = "Y"
		}

		r.DataType = *f.DataType
		if f.DataLength != nil {
			r.DataLength = *f.DataLength
		} else {
			r.DataLength = 20
		}

		if f.ColComment != nil {
			r.ColComment = *f.ColComment
		} else {
			r.ColComment = ""
		}

		if len(colKeyList) > 0 {
			for _, ck := range colKeyList {
				if r.ColName == ck {
					r.ColKey = "PRI"
					break
				}
			}
		}

		result = append(result, r)
	}
	return result, nil
}
